-- Step 0. Remove inconsistent data from the original database.
DO $$
BEGIN
	-- LOOP 5 times TO ensure all dependecies of non-existend data are removed.
	-- 303 Rows from posts_universal are removed.
	-- 616 Rows from comments_universal are removed.
	FOR i IN 1..5
	LOOP	
	-- Removes all the questions that refer to a non-existent linked question.
	DELETE FROM posts_universal WHERE linkpostid NOT IN (SELECT id FROM posts_universal);
	-- Removes all the questions that refer to a non-existent accepted answer.
	DELETE FROM posts_universal WHERE acceptedanswerid NOT IN (SELECT id FROM posts_universal);
	-- Removes all the answers that refer to a parentid of a non-existing question.
	DELETE FROM posts_universal WHERE parentid NOT IN (SELECT id FROM posts_universal);
	-- Remove all the questions that link to a non-question id.
	DELETE FROM posts_universal WHERE linkpostid NOT IN (SELECT id FROM posts_universal WHERE posttypeid = 1);
	-- Remove all the comments that do not link to a post id.
	DELETE FROM comments_universal WHERE postid NOT IN (SELECT DISTINCT id FROM posts_universal);
	END LOOP;
END;
$$;

-- Step 1. Find all the distinct locations and populate location table
INSERT INTO location(location) 
SELECT DISTINCT ownerlocation
FROM posts_universal
WHERE ownerlocation IS NOT NULL;



-- Step 2a. Populate the author table using the already populated locations
INSERT INTO author(id, display_name, creation_date, age, location_id)
SELECT DISTINCT ownerid, ownerdisplayname, ownercreationdate, ownerage, "location".id
FROM posts_universal
JOIN "location" ON posts_universal.ownerlocation = "location"."location";

-- Step 2b. Populate the author table with authors that have NULL location values
INSERT INTO author(id, display_name, creation_date, age)
SELECT DISTINCT ownerid, ownerdisplayname, ownercreationdate, ownerage
FROM posts_universal
WHERE ownerlocation IS NULL;



-- Step 3. Populate the answer table using the already populated author table with data from posts_universal.
-- Check if it still works. we changed the join condition.
INSERT INTO answer(id, body, score, creation_date, author_id)
SELECT DISTINCT posts_universal.id, body, score, creationdate, ownerid
FROM posts_universal
WHERE posttypeid = 2;



-- Step 4. Populate the question table using the already populated answer and author table with data from posts_universal.
INSERT INTO question(id, title, body, score, creation_date, closed_date, author_id, accepted_answer_id)
SELECT DISTINCT id, title, body, score, creationdate, closeddate, ownerid, acceptedanswerid
FROM posts_universal
WHERE posttypeid = 1;

-- Step 4b. After the question and answer tables have been created, we can proceed by updating the question_id (FK) with the referenced value (parentid / answer.id) in the answer table
UPDATE answer
SET question_id = posts_universal.parentid
FROM posts_universal
WHERE posts_universal.posttypeid = 2 
and answer.id = posts_universal.id; 


-- Step 5. Populate linked_question table with data from posts_universal table. 
INSERT INTO linked_question (question_id, linked_question_id)
SELECT id, linkpostid
FROM posts_universal
WHERE linkpostid IS NOT NULL
AND posttypeid = 1;

-- Step 6. Populate tag table with data from posts_universal table.
DO $$
DECLARE tag_row varchar(100)[];
		tag_string varchar(100) := '';
BEGIN
	FOR tag_row IN (SELECT DISTINCT regexp_split_to_array(tags, TEXT '::') FROM posts_universal WHERE tags IS NOT NULL)
	LOOP
		FOR i IN ARRAY_LOWER(tag_row, 1)..ARRAY_UPPER(tag_row, 1)
		LOOP
			tag_string := tag_row[i];
			IF NOT EXISTS(SELECT tag FROM tag WHERE tag.tag = tag_string) THEN
				INSERT INTO tag(tag) VALUES (tag_string); 
			END IF;
		END LOOP;
	END LOOP;
END;
$$;

-- Step 7. Populate question_tag WITH DATA FROM posts_universal table.
DO $$
DECLARE tag_number integer := (SELECT count(tag) FROM tag); -- the tag number
		tag_name varchar(100) := '';
		question_ids integer[];
BEGIN
	FOR i IN 1..tag_number
	LOOP
		tag_name := (SELECT tag FROM tag WHERE id = i);
		question_ids := ARRAY(
								SELECT DISTINCT id
								FROM posts_universal
								WHERE tags LIKE '%::' || tag_name || '::%'
								OR tags LIKE tag_name || '::%'
								OR tags LIKE '%::' || tag_name
								OR tags = tag_name
							 );
		FOR j IN ARRAY_LOWER(question_ids, 1)..ARRAY_UPPER(question_ids, 1)
		LOOP
			INSERT INTO question_tag(question_id, tag_id)
			VALUES (question_ids[j], i);
		END LOOP;
	END LOOP;
END;
$$;


-- Step 8a. Populate the location table with data from comments_universal
INSERT INTO location(location) 
SELECT DISTINCT authorlocation
FROM comments_universal
WHERE authorlocation IS NOT NULL
ON CONFLICT DO NOTHING;

-- Step 8b. Populate the author table with data from comments_universal
INSERT INTO author(id, display_name, creation_date, age, location_id)
SELECT DISTINCT authorid, authordisplayname, authorcreationdate, authorage, "location".id
FROM comments_universal
JOIN "location" ON comments_universal.authorlocation = "location"."location"
ON CONFLICT DO NOTHING; -- avoid conflicting duplicates.

-- Step 8c. Populate the author table with data from comments_universal that have a null location.
INSERT INTO author(id, display_name, creation_date, age)
SELECT DISTINCT authorid, authordisplayname, authorcreationdate, authorage
FROM comments_universal
WHERE authorlocation IS NULL
ON CONFLICT DO NOTHING; -- avoid conflicting duplicates.


-- Step 8d. Populate the COMMENT TABLE WITH DATA FROM comments_universal table.
-- Populate all the question comments:
INSERT INTO "comment" (id, "text", score, creation_date, author_id, question_id)
SELECT DISTINCT commentid, commenttext, commentscore, commentcreatedate, authorid, question.id
FROM comments_universal
JOIN question ON comments_universal.postid = question.id
ORDER BY commentid;
-- Populate ALL the answer COMMENTS:
INSERT INTO "comment" (id, "text", score, creation_date, author_id, answer_id)
SELECT DISTINCT commentid, commenttext, commentscore, commentcreatedate, authorid, answer.id
FROM comments_universal
JOIN answer ON comments_universal.postid = answer.id;